package net.alche.sdd.fatis;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.management.RuntimeErrorException;
import javax.naming.CommunicationException;
import javax.swing.Renderer;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.ReflectUtil;
import net.alche.al.util.far.pojo.PageList;
import net.alche.al.util.AC;
import net.alche.al.util.Far;
import net.alche.al.util.M;
import net.alche.al.util.far.pojo.ext.ThreadLocalNode;
import net.alche.fa.core.FaClass;
import net.alche.fa.core.FaException;
import net.alche.fa.core.FaUtil;
import net.alche.fa.core.model.FaValue;

@SuppressWarnings("all")
public class SddFatisStatement {
  public static final Logger logger = LoggerFactory.getLogger(SddFatisStatement.class);
  Connection conn = null;
  SddDataSourceConnection sddConn;
  SddFatis fatis = null;
  PreparedStatement stm = null;
  public boolean waitCloseFlag = false;
  int reCount = 0;

  public SddFatisStatement(SddFatis fatis, boolean isSdd, SddDataSourceConnection sddConn) {
    this.conn = sddConn.cnn;
    this.sddConn = sddConn;
    this.fatis = fatis;
  }

  public SddFatisStatement(SddFatis fatis, Connection conn) {
    this.conn = conn;
    this.fatis = fatis;
  }

  public void closeResultSet(ResultSet rs) {
    if (rs != null)
      try {
        rs.close();
      } catch (Exception ex) {
        ex.printStackTrace();
      }
  }

  public <T> T selectOne(String sql, Class cls, List<SddFatisPrepared> vars) {
    if (cls.equals(Integer.class) || cls.equals(Double.class) || cls.equals(String.class)
        || cls.equals(Long.class)) {
      List<Map<String, Object>> ls = select(sql, vars);
      if (!AC.empty(ls)) {
        Map<String, Object> mp = ls.get(0);
        Object firstVal = M.first(mp);
        if (firstVal != null) {
          if (cls.equals(Integer.class)) {
            if (firstVal instanceof Long flong) {
              return (T) ((Object) flong.intValue());
            }
          }
          return (T) firstVal;
        }
      }
    } else {
      List ls = selectList(sql, cls, vars);
      if (!AC.empty(ls))
        return (T) ls.get(0);
    }
    return null;
  }

  public FaValue selectOne(String sql, FaClass cls, List<SddFatisPrepared> vars) {
    List<Map<String, Object>> ls = select(sql, vars);
    if (!AC.empty(ls)) {
      Map<String, Object> mp = ls.get(0);
      String clsName = cls.getConf().getName();
      if (clsName.equals("int") || clsName.equals("double") || clsName.equals("String") || clsName.equals("long")) {
        Object firstVal = M.first(mp);
        return new FaValue(firstVal);
      } else {
        return new FaValue(FaUtil.mapToPojo(cls.getPlat(), cls.getConf().getName(), mp));
      }
    }
    return null;
  }

  public String getPageSqlSuf(String sql, PageList pageList) {
    if (sql.startsWith("@")) {
      return M.subI(sql, 1);
    }
    if (pageList != null) {
      int num = pageList.getNum();
      int size = pageList.getSize();
      if (num < 0) {
        num = 0;
      }
      return sql + " limit " + (num * size) + " , " + size;
    }
    int limitIndex = sql.lastIndexOf(" limit ");
    if (limitIndex > 0) {
      String suf = sql.substring(limitIndex);
      if (!suf.contains("'")) {
        return sql;
      }
    }
    return sql + " limit 0 , 10000";
  }

  public FaValue selectListByMapFlass(String sql, List<SddFatisPrepared> vars) {
    ThreadLocalNode node = Far.lc.popThrowdData();
    PageList pageList = null;
    if (node != null) {
      if (node.data instanceof PageList) {
        pageList = (PageList) node.data;
        this.waitCloseFlag = true;
      }
    }
    List<Map<String, Object>> ls = select(getPageSqlSuf(sql, pageList), vars);
    FaValue fav = new FaValue();
    if (ls != null && !ls.isEmpty()) {
      List list = new ArrayList<>();
      for (Map<String, Object> b : ls) {
        if (b != null) {
          Map<String, Object> toMp = new HashMap<>();
          for (Entry<String, Object> sub : b.entrySet()) {
            String attrName = AC.str.getHump(sub.getKey());
            toMp.put(attrName, sub.getValue());
          }
          list.add(new FaValue(toMp));
        }

      }

      fav.aiData(list);
      if (pageList != null && pageList.getNum() >= 0) {
        pageList.setFavList(fav);
        if (list.size() < pageList.getSize()) {
          pageList.setTotal(ls.size() + pageList.getNum() * pageList.getSize());
        } else {
          waitCloseFlag = false;
          pageList.setTotal(selectCount(sql, vars));
        }
      }
    }
    if (waitCloseFlag) {
      waitCloseFlag = false;
      autoCloseConn();
    }
    return fav;
  }

  public FaValue selectList(String sql, FaClass cls, List<SddFatisPrepared> vars) {
    ThreadLocalNode node = Far.lc.popThrowdData();
    PageList pageList = null;
    if (node != null) {
      if (node.data instanceof PageList) {
        pageList = (PageList) node.data;
        this.waitCloseFlag = true;
      }
    }
    sql = getPageSqlSuf(sql, pageList);
    List<Map<String, Object>> ls = select(sql, vars);
    FaValue fav = new FaValue();
    if (ls != null && !ls.isEmpty()) {
      List list = new ArrayList<>();
      String clsName = cls.getConf().getName();
      if (clsName.equals("int") || clsName.equals("string") || clsName.equals("double")) {
        for (Map b : ls) {
          Object obj = AC.getMapFirst(b);
          list.add(obj);
        }
      } else {
        for (Map b : ls) {
          list.add(FaUtil.mapToPojo(cls.getPlat(), cls.getConf().getName(), b));
        }
      }
      fav.aiData(list);
      if (pageList != null && pageList.getNum() >= 0) {
        pageList.setFavList(fav);
        if (list.size() < pageList.getSize()) {
          pageList.setTotal(ls.size() + pageList.getNum() * pageList.getSize());
        } else {
          waitCloseFlag = false;
          pageList.setTotal(selectCount(sql, vars));
        }
      }
    }
    if (waitCloseFlag) {
      waitCloseFlag = false;
      autoCloseConn();
    }
    return fav;
  }

  public List selectListByMap(String sql, Class cls, List<SddFatisPrepared> vars) {
    return selectList(sql, cls, vars);
  }

  public List selectList(String sql, Class cls, List<SddFatisPrepared> vars) {
    if (sql == null)
      return null;
    ThreadLocalNode node = Far.lc.popThrowdData();
    PageList pageList = null;
    if (node != null) {
      if (node.data instanceof PageList) {
        pageList = (PageList) node.data;
        this.waitCloseFlag = true;
      }
    }
    ResultSet rs = null;
    List ls = null;
    sql = getPageSqlSuf(sql, pageList);
    PreparedStatement stt = getStatement(sql);
    fillPrepared(stt, vars);
    try {
      rs = stt.executeQuery();
      ResultSetMetaData md = rs.getMetaData();
      int columnCount = md.getColumnCount();
      int i = 0;
      ls = new ArrayList<>();
      Object bean = null;
      Map mp = null;
      boolean isMap = false;
      if (cls.equals(Map.class)) {
        isMap = true;
      }
      String[] humpName = new String[columnCount];
      for (i = 1; i <= columnCount; i++) {
        humpName[i - 1] = AC.str.getHump(md.getColumnLabel(i));
      }
      if (cls.equals(Integer.class) || cls.equals(String.class) || cls.equals(Double.class)) {
        while (rs.next()) {
          ls.add(rs.getObject(1));
        }
      } else {
        if (isMap) {
          while (rs.next()) {
            mp = new HashMap<>();
            for (i = 1; i <= columnCount; i++) {
              Object obj = rs.getObject(i);
              if (obj != null && obj instanceof LocalDateTime ldd) {
                Instant instant = ldd.atZone(ZoneId.systemDefault()).toInstant();
                obj = Date.from(instant);
              }
              mp.put(humpName[i - 1], obj);
            }
            ls.add(mp);
          }
        } else {
          while (rs.next()) {
            bean = (Object) cls.getDeclaredConstructor().newInstance();
            for (i = 1; i <= columnCount; i++) {
              try {
                if (humpName[i - 1] != null) {
                  ReflectUtil.setFieldValue(bean, humpName[i - 1], rs.getObject(i));
                }
              } catch (IllegalArgumentException ie) {
                humpName[i - 1] = null;
              } catch (Exception ex) {
                ex.printStackTrace();
              }
            }
            ls.add(bean);
          }
        }
      }
      // rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
      Far.logError("SddFatisStatement,[SQL-ERR]", getLogSql(sql, vars));
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeResultSet(rs);
      autoCloseConn();
    }
    logSql("SELECT", sql, vars);
    if (pageList != null && pageList.getNum() >= 0) {
      pageList.setList(ls);
      if (ls != null && ls.size() < pageList.getSize()) {
        pageList.setTotal(ls.size() + pageList.getNum() * pageList.getSize());
      } else {
        waitCloseFlag = false;
        pageList.setTotal(selectCount(sql, vars));
      }
    }
    if (waitCloseFlag) {
      waitCloseFlag = false;
      autoCloseConn();
    }
    return ls;
  }

  public PreparedStatement getStatement(String sql) {
    return getStatement(sql, false);
  }

  public PreparedStatement getStatement(String sql, boolean isReturnGennerKey) {
    // if (stm != null) {
    // return stm;
    // }
    if (conn == null) {
      return null;
    }
    PreparedStatement stt = null;
    try {
      if (isReturnGennerKey) {
        stt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
      } else {
        stt = conn.prepareStatement(sql);
      }
      stm = stt;
      return stt;
    } catch (Exception ex) {
      // ex.printStackTrace();
      String errMsg = ex.getLocalizedMessage();
      Error err = new Error(errMsg);
      logger.error("【SQL-ERR】" + sql);
      logger.error("【SQL-ERR-MSG】" + errMsg);
      throw new RuntimeErrorException(err, err.getMessage());
    }
    // return null;
  }

  void fillPrepared(PreparedStatement stt, List<SddFatisPrepared> vars) {
    if (vars == null)
      return;
    try {
      int i = 1;
      for (SddFatisPrepared obj : vars) {
        if (obj.val != null) {
          if (obj.val instanceof Date valDate) {
            stt.setString(i++, AC.date.fmtDatetime(valDate));
          } else
            stt.setString(i++, obj.val.toString());
        } else {
          stt.setNull(i++, java.sql.Types.VARCHAR);
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public String getLogSql(String sql, List<SddFatisPrepared> vars) {
    String[] sp = sql.split("\\?(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)", -1);
    String toSql = "";
    if (vars == null) {
      return sql;
    }
    for (int i = 0; i < sp.length && i <= vars.size(); i++) {
      if (i > 0)
        toSql += vars.get(i - 1).getLogStr();
      toSql += sp[i];
    }
    return toSql;
  }

  public void logSql(String type, String sql, List<SddFatisPrepared> vars) {
    if (fatis.logSql) {
      if (fatis.logSqlNoSelect && (type.equals("SELECT") || type.equals("SELECT-COUNT"))) {
        return;
      }
      logger.info("Fatis-SQL [{}]: {}", type, getLogSql(sql, vars));
    }
  }

  public int delete(String sql, List<SddFatisPrepared> vars) {
    if (sql == null) {
      return 0;
    }
    int count = 0;
    try {
      PreparedStatement stt = getStatement(sql);
      fillPrepared(stt, vars);
      count = stt.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      autoCloseConn();
    }
    logSql("DELETE", sql, vars);
    return count;
  }

  public int update(String sql, List<SddFatisPrepared> vars) {
    if (sql == null) {
      return 0;
    }
    int count = 0;
    PreparedStatement stt = getStatement(sql);
    fillPrepared(stt, vars);
    try {
      if (stt != null) {
        count = stt.executeUpdate();
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      autoCloseConn();
    }
    logSql("UPDATE", sql, vars);
    return count;
  }

  public int insert(String sql, List<SddFatisPrepared> vars, SddFatisMacro fm) {
    if (sql == null) {
      return 0;
    }
    int count = 0;
    PreparedStatement stt = getStatement(sql, fm.getAutoIdCol() != null);
    fillPrepared(stt, vars);
    try {
      count = stt.executeUpdate();
      if (fm.getAutoIdCol() != null) {
        // 获取自增id
        int id = 0;
        ResultSet resultSet = stt.getGeneratedKeys();
        while (resultSet.next()) {
          id = resultSet.getInt(1);
          fm.setAutoIdVal(id);
        }
        resultSet.close();
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      // closeResultSet(rs);
      autoCloseConn();
    }
    logSql("INSERT", sql, vars);
    return count;
  }

  public long selectCount(String fullSql, List<SddFatisPrepared> vars) {
    ResultSet rs = null;
    String countSql = "select count(*)";
    // fullSql = fullSql.replaceAll("" + (char) 10, " ");
    String[] sp = fullSql.split("from ");
    boolean isStart = false;
    boolean isFirst = true;
    for (int i = 0; i < sp.length; i++) {
      String str = sp[i];
      // for (String str : s p) {
      if (isFirst) {
        isFirst = false;
        continue;
      }
      if (!isStart && !str.contains(" as ")) {
        isStart = true;
      } else if (isStart && str.contains(" as ")) {
        isStart = false;
        countSql = "select count(*)";
      }
      if (str.contains(" limit ")) {
        str = M.subPre(str, " limit ");
      }
      // AC.log(str);
      if (isStart) {
        countSql += " from " + str;
      }
    }
    if (countSql.contains(" order by ")) {
      int lastIndex = countSql.lastIndexOf(" order by ");
      String pre = countSql.substring(0, lastIndex);
      String after = countSql.substring(lastIndex, countSql.length());
      if (after.contains(" and ") || after.contains(" from ") || after.contains(" select ")) {

      } else {
        countSql = pre;
      }
    }

    long count = 0;
    try {
      PreparedStatement stt = getStatement(countSql);
      if (vars != null) {
        int withVarCount = M.withCount(countSql, "?");
        if (withVarCount != vars.size()) {
          while (withVarCount < vars.size()) {
            vars.remove(0);
          }
        }
      }
      fillPrepared(stt, vars);
      rs = stt.executeQuery();
      if (rs.next()) {
        count = rs.getLong(1);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeResultSet(rs);
      autoCloseConn();
    }
    logSql("SELECT-COUNT", countSql, vars);
    return count;
  }

  public List<Map<String, Object>> select(String sql, List<SddFatisPrepared> vars) {
    List<Map<String, Object>> ls = new ArrayList<>();
    ResultSet rs = null;
    PreparedStatement stt = getStatement(sql);
    fillPrepared(stt, vars);
    try {
      long startMille = System.currentTimeMillis();
      if (stt != null) {
        rs = stt.executeQuery();
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();
        Map mp = null;
        int i = 0;
        while (rs.next()) {
          mp = new HashMap<>();
          for (i = 1; i <= columnCount; i++) {
            mp.put(md.getColumnLabel(i), rs.getObject(i));
          }
          ls.add(mp);
        }
      }
      // startMille = System.currentTimeMillis() - startMille;
      // AC.log("select-times",startMille);
    } catch (Exception e) {
      e.printStackTrace();
      logger.error(sql);
    } finally {
      closeResultSet(rs);
      autoCloseConn();
    }
    logSql("SELECT", sql, vars);
    return ls;
  }

  public List<Map> select(String sql) {
    List<Map> ls = new ArrayList<>();
    ResultSet rs = null;
    try {
      rs = getStatement(sql).executeQuery();
      ResultSetMetaData md = rs.getMetaData();
      int columnCount = md.getColumnCount();
      Map mp = null;
      int i = 0;
      if (rs.next()) {
        mp = new HashMap<>();
        for (i = 1; i <= columnCount; i++) {
          mp.put(md.getColumnLabel(i), rs.getObject(i));
        }
        ls.add(mp);
      }
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeResultSet(rs);
      autoCloseConn();
    }
    return ls;
  }

  public void autoCloseConn() {
    closeStatement();
    if (!this.waitCloseFlag) {
      closeConn();
    }
  }

  public void closeStatement() {
    if (stm != null) {
      try {
        stm.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public void closeConn() {
    if (sddConn != null) {
      sddConn.sendBackPool();
      return;
    }
    if (conn == null) {
      return;
    }
    try {
      conn.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void close() {
    closeStatement();
    closeConn();
  }
}
